import sqlite3 from 'sqlite3';
import { MemoryRecord, ErrorPattern, SearchParams } from './types.js';
import { promisify } from 'util';

export class DatabaseManager {
    private db: sqlite3.Database;
    private runAsync: (sql: string, params?: any[]) => Promise<any>;
    private allAsync: (sql: string, params?: any[]) => Promise<any[]>;

    constructor(dbPath: string = ':memory:') {
        this.db = new sqlite3.Database(dbPath);
        this.runAsync = promisify(this.db.run.bind(this.db));
        this.allAsync = promisify(this.db.all.bind(this.db));
        this.initDatabase();
    }

    /**
     * 初始化数据库表
     */
    private async initDatabase(): Promise<void> {
        // 创建记忆记录表
        await this.runAsync(`
            CREATE TABLE IF NOT EXISTS memories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                type TEXT NOT NULL,
                content TEXT NOT NULL,
                context TEXT,
                timestamp TEXT NOT NULL,
                tags TEXT
            )
        `);

        // 创建错误模式表
        await this.runAsync(`
            CREATE TABLE IF NOT EXISTS error_patterns (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                pattern TEXT NOT NULL UNIQUE,
                frequency INTEGER DEFAULT 1,
                solutions TEXT,
                last_updated TEXT NOT NULL
            )
        `);

        // 创建索引
        await this.runAsync('CREATE INDEX IF NOT EXISTS idx_memories_type ON memories(type)');
        await this.runAsync('CREATE INDEX IF NOT EXISTS idx_memories_timestamp ON memories(timestamp)');
        await this.runAsync('CREATE INDEX IF NOT EXISTS idx_error_patterns_pattern ON error_patterns(pattern)');
    }

    /**
     * 记录新的记忆
     */
    async recordMemory(memory: MemoryRecord): Promise<number> {
        const result = await this.runAsync(
            `INSERT INTO memories (type, content, context, timestamp, tags)
             VALUES (?, ?, ?, ?, ?)`,
            [
                memory.type,
                memory.content,
                memory.context || null,
                memory.timestamp,
                memory.tags || null
            ]
        );

        return (result as any).lastID;
    }

    /**
     * 查询记忆记录
     */
    async queryMemories(params: SearchParams): Promise<MemoryRecord[]> {
        let query = 'SELECT * FROM memories WHERE 1=1';
        const queryParams: any[] = [];

        // 修改类型比较，允许 'all' 作为有效值
        if (params.type && params.type !== 'all') {
            query += ' AND type = ?';
            queryParams.push(params.type);
        }

        if (params.searchText) {
            query += ' AND (content LIKE ? OR context LIKE ?)';
            const keyword = `%${params.searchText}%`;
            queryParams.push(keyword, keyword);
        }

        if (params.tags) {
            query += ' AND tags LIKE ?';
            queryParams.push(`%${params.tags}%`);
        }

        query += ' ORDER BY timestamp DESC';

        if (params.limit) {
            query += ' LIMIT ?';
            queryParams.push(params.limit);
        }

        const rows = await this.allAsync(query, queryParams);
        return rows as MemoryRecord[];
    }

    /**
     * 更新错误模式
     */
    async updateErrorPattern(pattern: string, solution?: string): Promise<void> {
        await this.runAsync(
            `INSERT INTO error_patterns (pattern, frequency, solutions, last_updated)
             VALUES (?, 1, ?, datetime('now'))
             ON CONFLICT(pattern) DO UPDATE SET
             frequency = frequency + 1,
             solutions = COALESCE(?, solutions),
             last_updated = datetime('now')`,
            [pattern, solution, solution]
        );
    }

    /**
     * 获取错误模式
     */
    async getErrorPatterns(limit?: number): Promise<ErrorPattern[]> {
        let query = `
            SELECT pattern, frequency, solutions, last_updated as lastUpdated
            FROM error_patterns
            ORDER BY frequency DESC
        `;

        if (limit) {
            query += ' LIMIT ?';
            const rows = await this.allAsync(query, [limit]);
            return rows as ErrorPattern[];
        } else {
            const rows = await this.allAsync(query);
            return rows as ErrorPattern[];
        }
    }

    /**
     * 按时间范围获取记忆
     */
    async getMemoriesByTimeframe(type: string, timeframe: string): Promise<MemoryRecord[]> {
        let timeInterval: string;
        switch (timeframe) {
            case 'day':
                timeInterval = '-1 day';
                break;
            case 'week':
                timeInterval = '-7 days';
                break;
            case 'month':
                timeInterval = '-30 days';
                break;
            default:
                timeInterval = '-100 years'; // 实际上是所有记录
        }

        const query = `
            SELECT *
            FROM memories
            WHERE type = ?
            AND timestamp >= datetime('now', ?)
            ORDER BY timestamp DESC
        `;

        const rows = await this.allAsync(query, [type, timeInterval]);
        return rows as MemoryRecord[];
    }

    /**
     * 关闭数据库连接
     */
    async close(): Promise<void> {
        const close = promisify(this.db.close.bind(this.db));
        await close();
    }
}